/*
 *	This file is part of Lounge-Cast.
 *
 *  Author:  	Markus Moormann
 *	E-mail:  	mmoorman@uni-osnabrueck.de
 *	Copyright:	University of Osnabrueck (Zentrum Virtuos)
 * 	Website:	www.uni-osnabrueck.de
 *	Date:		16.02.2011
 *
 *	Lounge-Cast is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *	Lounge-Cast is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Lounge-Cast.  If not, see <http://www.gnu.org/licenses/>.
 */
/**
 * 
 */
package backend.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import backend.dbConnection.ConnectionHelper;
import backend.vo.MediaVO;
import backend.vo.PlayerVO;
import backend.vo.PlaylistVO;
import backend.vo.UserVO;

/**
 * class for playlist service
 * 
 * @author Markus
 */
public class PlaylistService {

	/**
	 * a logger to log errors, debug information, etc.
	 */
	private static Logger LOG = Logger.getLogger(PlaylistService.class);
	/**
	 * the connection to database
	 */
	//private Connection connection;
	/**
	 * the statement so send to database
	 */
	//private Statement statement;
	
	/**
	 * static function for adding a playlist 
	 * convenience method for FeedService
	 * 
	 * @param playlist the playlist to add
	 * @return the created key in database
	 */
	public static int addPlaylist(PlaylistVO playlist)
	{
		Connection connection = null;
		Statement statement = null;
		int playlist_id = 0;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			//insert playlist and get generated playlist_id
			String insertPlaylist = "INSERT INTO `media_playlist` " +
										"(" +
											"`inheritance_level`," +
											"`inheritance_from`," +
											"`description`" +
										")VALUES" +
										"(" +
											playlist.inheritance_level + "," +
											(playlist.inheritance_from == null ? "NULL" : playlist.inheritance_from.playlist_id) + "," +
											"'" + playlist.description + "'" +
										")";
			statement.execute(insertPlaylist, Statement.RETURN_GENERATED_KEYS);
			//get generated key
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			playlist_id = rs.getInt(1);
		} catch(SQLException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return playlist_id;
	}
	
	/**
	 * saves a playlist to DB
	 * @param playlist the playlist to save
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean savePlaylist(PlaylistVO playlist, UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			//insert playlist and get generated playlist_id
			String insertPlaylist = "INSERT INTO `media_playlist` " +
										"(" +
											"`inheritance_level`," +
											"`inheritance_from`," +
											"`description`" +
										")VALUES" +
										"(" +
											playlist.inheritance_level + "," +
											(playlist.inheritance_from == null ? "NULL" : playlist.inheritance_from.playlist_id) + "," +
											"'" + playlist.description + "'" +
										")";
			String insertPlaylistToPlayer = "INSERT INTO " +
												"media_playlist_user " +
											"SET " +
												"user_id = " + user.user_id  + "," +
												"playlist_id = ";
												
			statement.execute(insertPlaylist, Statement.RETURN_GENERATED_KEYS);
			//get generated key
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			int playlist_id = rs.getInt(1);
			//insert all playlist_items at once
			String insertPlaylistItems = 	"INSERT INTO `media_playlist_items` " +
												"(" +
													"`playlist_id`," +
													"`media_id`," +
													"`position`" +
												")VALUES";
			for(int i = 0; i < playlist.media.size(); i++)
			{
				insertPlaylistItems += 	"(" +
											playlist_id + "," +
											"'" + playlist.media.get(i).media_id + "'," +
											playlist.media.get(i).position + 
										")";
				if(i != (playlist.media.size() - 1)) {
					insertPlaylistItems += ",";
				}
			}
			statement.execute(insertPlaylistItems);
			statement.execute(insertPlaylistToPlayer + playlist_id);
			return true;
												
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
	}
	
	/**
	 * get all playlists for a user
	 * 
	 * @return a <code>List&lt;PlaylistVO&gt;</code>
	 */
	public List<PlaylistVO> getPlaylists(UserVO user) {
		List<PlaylistVO> result = new ArrayList<PlaylistVO>();
		String selectPlaylists = "SELECT " +
									"`p`.`playlist_id`," +
									"`p`.`description`," +
									"`p`.`inheritance_level`," +
									"`p`.`inheritance_from`," +
									"`m`.`media_id`," +
									"`m`.`type`," +
									"`m`.`title`," +
									"UNIX_TIMESTAMP(`m`.`start_date`) AS `start_date`," +
									"UNIX_TIMESTAMP(`m`.`valid_date`) AS `valid_date`," +
									"`m`.`newsticker`," +
									"`m`.`duration`," +
									"`m`.`reference`," +
									"UNIX_TIMESTAMP(`m`.`added`) AS `added`," +
									"`m`.`added_by`," +
									"`m`.`approved`," +
									"`i`.`position` " +
								"FROM " +
									"`media_playlist` `p` " +
								"LEFT JOIN" +
									"`media_playlist_items` `i` " +
										"ON(`p`.`playlist_id` = `i`.`playlist_id`)" +
								"LEFT JOIN" +
									"`media` `m` " +
										"ON (`i`.`media_id` = `m`.`media_id`)" +
								(user == null || user.user_group.group_id == 3 || user.user_group.group_id == 4 ?
									"" : 
									"WHERE " +
										"`p`.`playlist_id` IN " +
											"(" +
												"SELECT " +
													"playlist_id " +
												"FROM " +
													"media_playlist_user " +
												"WHERE " +
													"user_id = " + user.user_id +
											") "
								) +
								"ORDER BY " +
									"`p`.`description` ASC," +
									"`i`.`position` ASC";
		
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(selectPlaylists);
			int lastPlaylistId = 0;
			List<MediaVO> media = new ArrayList<MediaVO>();
			PlaylistVO playlist = new PlaylistVO();
			int playlist_id = 0;
			while(rs.next()) {
				if(rs.getInt("playlist_id") != lastPlaylistId) {
					lastPlaylistId = rs.getInt("playlist_id");
					if(playlist.description != null)
					{
						playlist.media = media;
						result.add(playlist);
						media = new ArrayList<MediaVO>();
					}
					
					playlist = new PlaylistVO();
					playlist_id = rs.getInt("playlist_id");
					playlist.description = rs.getString("description");
					playlist.inheritance_from = getPlaylist(rs.getInt("inheritance_from"));
					playlist.inheritance_level = rs.getInt("inheritance_level");
					playlist.playlist_id = rs.getInt("playlist_id");
				}
				if(rs.getString("media_id") != null)
				{
					MediaVO tmpMedia = new MediaVO(rs.getString("media_id"), 
							   rs.getString("type"), 
							   rs.getString("title"), 
							   rs.getLong("start_date"),
							   rs.getLong("valid_date"), 
							   rs.getBoolean("newsticker"), 
							   rs.getLong("duration"), 
							   rs.getString("reference"));
					tmpMedia.playlist_id = playlist_id;
					tmpMedia.position = rs.getFloat("position");
					tmpMedia.added = rs.getLong("added");
					tmpMedia.approved = rs.getBoolean("approved");
					tmpMedia.addedBy = new UserVO(rs.getInt("added_by"), null, null);
					tmpMedia = new MediaService().getMediaContent(tmpMedia);
					media.add(tmpMedia);
				}
			}
			if(playlist.description != null)
			{
				playlist.media = media;
				result.add(playlist);
			}
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			e.printStackTrace();
			return null;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return result;
	}
	
	/**
	 * get playlists for a given player
	 * 
	 * @param player the player to get the playlists from
	 * @return a <code>List&lt;PlaylistVO&gt;</code>
	 */
	public List<PlaylistVO> getPlaylistsForPlayer(PlayerVO player) {
		List<PlaylistVO> result = new LinkedList<PlaylistVO>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectPlaylists = "SELECT " +
										"p.playlist_id," +
										"inheritance_level," +
										"inheritance_from," +
										"description " +
									"FROM " +
										"media_playlist p," +
										"playlist_player pp " +
									"WHERE " +
										"pp.player_id = '" + player.player_id + "' AND " +
										"p.playlist_id = pp.playlist_id";
			ResultSet rs = statement.executeQuery(selectPlaylists);
			while(rs.next()) {
				PlaylistVO playlist = new PlaylistVO();
				playlist.playlist_id = rs.getInt("playlist_id");
				playlist.inheritance_level = rs.getInt("inheritance_level");
				playlist.inheritance_from = getPlaylist(rs.getInt("inheritance_from"));
				playlist.description = rs.getString("description");
				result.add(playlist);
			}
		} catch (SQLException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return result;
	}
	
	/**
	 * get the playlists by given id
	 * @param playlist_id
	 * @return <code>PlaylistVO</code>
	 */
	public PlaylistVO getPlaylist(int playlist_id) {
		if(playlist_id == 0) {
			return null;
		}
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectPlaylist = "SELECT " +
										"playlist_id," +
										"inheritance_level," +
										"inheritance_from," +
										"description " +
									"FROM " +
										"media_playlist " +
									"WHERE " +
										"playlist_id = " + playlist_id;
			ResultSet rs = statement.executeQuery(selectPlaylist);
			if(rs.next()) {
				PlaylistVO playlist = new PlaylistVO();
				playlist.playlist_id = rs.getInt("playlist_id");
				playlist.inheritance_level = rs.getInt("inheritance_level");
				playlist.inheritance_from = getPlaylist(rs.getInt("inheritance_from"));
				playlist.description = rs.getString("description");
				return playlist;
			}
		} catch (SQLException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return null;
	}
	
	/**
	 * delete a playlist from database
	 * 
	 * @param playlist the playlist to delete
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean deletePlaylist(PlaylistVO playlist) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String delete = "DELETE FROM " +
								"media_playlist " +
							"WHERE " +
								"playlist_id = " + playlist.playlist_id;
			
			statement.execute(delete);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * update data of a playlist in database
	 * @param playlist the playlist with its new data
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updatePlaylist(PlaylistVO playlist) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			//start transaction
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			//update playlistData
			String updatePlaylist = "UPDATE " +
										"`media_playlist` " +
									"SET " +
										"`inheritance_level` =  " + playlist.inheritance_level + "," +
										"`inheritance_from`  = " + (playlist.inheritance_from == null ? "NULL" : playlist.inheritance_from.playlist_id) + "," +
										"`description`		 = '" + playlist.description + "' " +
									"WHERE " +
										"`playlist_id` = " + playlist.playlist_id;
			statement.execute(updatePlaylist);
			//delete all PlaylistItems from playlist
			String deletePlaylistItems = "DELETE FROM " +
										 	"media_playlist_items " +
										  "WHERE " +
										  	"playlist_id = " + playlist.playlist_id;
			statement.execute(deletePlaylistItems);
			//insert all playlist_items at once
			String insertPlaylistItems = 	"INSERT INTO `media_playlist_items` " +
												"(" +
													"`playlist_id`," +
													"`media_id`," +
													"`position`" +
												")VALUES";
			for(int i = 0; i < playlist.media.size(); i++)
			{
				insertPlaylistItems += 	"(" +
											playlist.playlist_id + "," +
											"'" + playlist.media.get(i).media_id + "'," +
											playlist.media.get(i).position + 
										")";
				if(i != (playlist.media.size() - 1)) {
					insertPlaylistItems += ",";
				}
			}
			if(playlist.media.size() > 0) {
				
				String updatePlayer = 	"UPDATE " +
											"player " +
										"SET " +
											"last_update = NOW() " +
										"WHERE " +
											"player_id IN " +
											"(" +
												"SELECT " +
													"player_id " +
												"FROM " +
													"playlist_player " +
												"WHERE " +
													"playlist_id = " + playlist.playlist_id +
											")";
				
				statement.execute(insertPlaylistItems);
				statement.execute(updatePlayer);
			}
			connection.commit();
			
		} catch (SQLException e) {
			LOG.error(e);
			try {
				connection.rollback();
			} catch (SQLException ex) {
				LOG.error(ex);
			}
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return true;
	}
	
}
